<!DOCTYPE html>
<html>
    <head>
        <title>Pick-up Game</title>
        <link rel="stylesheet" href="style.css" type="text/css" />
        <script src="http://code.jquery.com/jquery-latest.js"></script>
	<script type="text/javascript" src="http://jzaefferer.github.com/jquery-validation/jquery.validate.js"></script>
	<script>
	    $(document).ready(function(){
		$("#players").validate();
	    });
	</script>
    </head>

    <body>

        <?php
        include_once('menu.php');
        
        // database connection strings
        $conn_str = 'host=dbsrv1.cdf.toronto.edu dbname=csc309g23 user=csc309g23 password=ipheid5h';
        $conn = pg_connect($conn_str);
        
        // query database
        $query = 'SELECT * FROM sports';
        $result = pg_query($conn, $query);
        ?>

        <div class="container">
        <form action="players.php?go" method="post" id="players">
	    <h2>Players</h2>
	    <hr></hr>
                <table>
                    <tr><td>Sports Played</td><td><select name="sport">
				    <option>All</option>
                                <?php while ($row = pg_fetch_row($result)) { ?>
                                    <option><?= $row[0] ?></option>
                                <?php } ?></select></td></tr>
                    <tr><td>Min Skill</td><td><select name="skillmin">
                                <option selected="selected">0</option>
                                <option>1</option>
                                <option>2</option>
                                <option>3</option>
                                <option>4</option>
                            </select></td>
                    </tr>
		    <tr><td>Max Skill</td><td><select name="skillmax">
                                <option>1</option>
                                <option>2</option>
                                <option>3</option>
                                <option>4</option>
                                <option selected="selected">5</option>
                            </select></td>
                    </tr>
                    <tr><td>First Name</td><td><input type="text" name="fname" /></td></tr>
                    <tr><td>Last Name</td><td><input type="text" name="lname" /></td></tr>
                    <tr><td>City</td><td><input type="text" name="city"></td></tr>    
                    
                            <?php // create countries dropdown
			    $query = 'SELECT * FROM countries';
			    $result = pg_query($conn, $query);
			    ?>
			    <tr><td>Country</td>
                                <td><select name="country">
				    <option value=''>All</option>
                                        <?php while ($row = pg_fetch_row($result)) { ?>
                                            <option><?= $row[0] ?></option>
                                        <?php } ?>
                                    </select>
                            </td></tr>
                            
                    <tr><td></td><td><input name="submit" type="submit" value="search"></td></tr>
                </table>
        </form>
	<h2>Results</h2>
        <hr></hr>
            <table class="displayTable">
                <tr>
                    <td>Sport</td>
                    <td>Skill</td>
                    <td>First Name</td>
		    <td>Last Name</td>
		    <td>Username</td>
                    <td>City</td>
                    <td>Country</td>
                    <td>Game</td>
                </tr>
        <?php
	if (isset($_POST['submit'])){
		
		$skillfieldmin = 0;
		$skillfieldmax = 5;
		$sportfield = '%';
		$fnfield = '%';
		$lnfield = '%';
		$cityfield = '%';
		$countryfield = '%';
		$loccheck = '';
		if (isset($_POST['sport'])&&($_POST['sport']!='All')) { $sportfield=$_POST['sport']; }
		if (isset($_POST['skillmin'])&&($_POST['skillmin']!=0)) { $skillfieldmin=$_POST['skillmin'];}
		if (isset($_POST['skillmax'])&&($_POST['skillmax']!=0)) { $skillfieldmax=$_POST['skillmax'];}
		if (isset($_POST['fname'])&&($_POST['fname']!='')){ $fnfield = $_POST['fname']; }
		if (isset($_POST['lname'])&&($_POST['lname']!='')){ $lnfield = $_POST['lname']; }
		if (isset($_POST['city'])||isset($_POST['country'])){			
			if($_POST['city']!=''){ $loccheck = $loccheck.'AND city=\''.$_POST['city'].'\''; }
			if($_POST['country']!=''){ $loccheck = $loccheck.'AND country=\''.$_POST['country'].'\''; }
		    }
		if (isset($_POST['sport'])&&($_POST['sport']!='All')) {
		$query = 'select * from members,availability WHERE members.username=availability.username AND sport LIKE \''.$sportfield.'\'AND playrating >'.$skillfieldmin.' AND playrating <'.$skillfieldmax.' AND firstname LIKE \''.$fnfield.'\' AND lastname LIKE \''.$lnfield.'\''.$loccheck;
		} else {
		$query = 'select * from members WHERE firstname LIKE \''.$fnfield.'\'AND playrating >'.$skillfieldmin.' AND playrating <'.$skillfieldmax.' AND lastname LIKE \''.$lnfield.'\''.$loccheck;
		}
		$query2 = 'select* from members,availability WHERE members.username=availability.username AND sport LIKE \''.$sportfield.'\' AND firstname LIKE \''.$fnfield.'\' AND lastname LIKE \''.$lnfield.'\' AND city LIKE \''.$cityfield.'\' AND country LIKE \''.$countryfield.'\'';
	    	//$query = 'select * from members';
		//echo $query;
		
		$result = pg_query($conn, $query);
		$arr = pg_fetch_all($result);
		$fullarr = array();
		
		foreach ($arr as &$a_row) {
		    //echo "-----/n";
		   // print_r($a_row["username"]);
		    //echo ">>>>> SPORTS >>>:/n";
		    $sportquery = 'select sport FROM members,availability WHERE members.username=availability.username AND members.username=\''.$a_row['username'].'\'';
		    $sportres = pg_query($conn, $sportquery);
		    $sportarr = pg_fetch_all_columns($sportres,0);
		    $sportsplayed = implode(", ",$sportarr);
		    $fullarr[$a_row["username"]] = $sportsplayed;
		   // echo $sportsplayed."/n";
		    //print_r($fullarr);
		}
		
	} else { 
		$query = 'select * from members';
		$query2 = 'select members.username, availability.username, sport, firstname, lastname, city ,country FROM members,availability WHERE members.username=availability.username';
		//echo $query;
		$result = pg_query($conn, $query);
		$arr = pg_fetch_all($result);
		$fullarr = array();
		foreach ($arr as &$a_row) {
		    //echo "-----/n";
		    //print_r($a_row["username"]);
		    //echo ">>>>> SPORTS >>>:/n";
		    $sportquery = 'select sport FROM members,availability WHERE members.username=availability.username AND members.username=\''.$a_row['username'].'\'';
		    $sportres = pg_query($conn, $sportquery);
		    $sportarr = pg_fetch_all_columns($sportres,0);
		    $sportsplayed = implode(", ",$sportarr);
		    $fullarr[$a_row["username"]] = $sportsplayed;
		    //echo $sportsplayed."/n";
		    //print_r($fullarr);
		}
	}
	
	?>
	<?php 
	    while ($players = pg_fetch_assoc($result)) {
                        ?>

                        <tr class="hand">
                            <td><?= $fullarr[$players['username']] ?></td>
                            <td><?= $players['playrating'] ?></td>
                            <td><?= $players['firstname'] ?></td>                           
                            <td><?= $players['lastname'] ?></td>
			    <td><?= $players['username'] ?></td>
                            <td><?= $players['city'] ?></td>
			    <td><?= $players['country'] ?></td>
			    <td><form action="invite.php" method="post">
			    <select name="gameid">
                                        <?php foreach ($_SESSION['games'] as $value) { ?>
                                            <option><?= $value ?></option>
                                        <?php } ?>
                            </select>
                            <input type="hidden" name="username" value="<?= $players['username'] ?>">
                            <input type="submit" value="invite"></form></td>
                        </tr>
                    <?php } ?>
            </table>
    </div>

</body>

</html>
